﻿#region 
using System;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
using System.Collections;
using System.Text;
using System.IO;
//using Oracle.DataAccess.Client;
using System.Security.Cryptography;
using System.Xml;
#endregion

namespace YTBZ
{
    public class DB
    {
        #region AppSettings
        public string pubGetAppSettings(string strProIN)
        {
            return ConfigurationManager.AppSettings[strProIN].ToString();
        }
        #endregion

        #region 数据库操作

        #region dt opt
        public DataSet proGetDS(SortedList SLData, string spName)
        {
            DataSet ds = HelperQuery(spName, SLData);
            return ds;
        }
        public DataTable proGetDT(SortedList SLData, string spName)
        {
            DataSet ds = proGetDS(SLData, spName);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }
        public string proGetDTOne(SortedList SLData, string spName)
        {
            DataSet ds = proGetDS(SLData, spName);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            else
            {
                return "";
            }
        }
        public string proSetDT(SortedList SLData, string spName)
        {
            object objBack = HelperExec(spName, SLData);
            return objBack == null ? "" : objBack.ToString();
        }
        #endregion

        #region 执行
        private object HelperExec(string cmdText, SortedList SLData)
        {
            string connStr = ConfigurationManager.AppSettings["YTBZ_CONN"].ToString();
            OracleConnection conn = null;
            object objBack = null;
            try
            {
                #region cmdExec
                OracleCommand cmd = new OracleCommand();
                conn = new OracleConnection(connStr);
                conn.Open();
                if (SLData == null)
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = cmdText;
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    OracleParameter[] commandParameters = getParame(conn, cmdText);
                    setParame(SLData, ref commandParameters);

                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = cmdText;
                    if (commandParameters != null)
                    {
                        foreach (OracleParameter parm in commandParameters)
                        { 
                            cmd.Parameters.Add(parm);
                        }
                    }
                    cmd.ExecuteNonQuery();
                    for (int i = 0; i < commandParameters.Length; i++)
                    {
                        if (commandParameters[i].Direction == ParameterDirection.Output
                            || commandParameters[i].Direction == ParameterDirection.InputOutput)
                        {
                            objBack = commandParameters[i].Value.ToString().Split('$')[0];
                            //txtSaveErro(cmdText+ objBack.ToString()+ commandParameters[i].Size);
                        }
                    }
                }
                return objBack;
                #endregion
            }
            catch (Exception ex)
            {
                txtSaveErro(" 存储过程 " + cmdText + " 参数 " + SDListToStr(SLData) + " 错误 " + ex.ToString());
                throw ex;
            }
            finally
            {
                #region connClose
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    try
                    {
                        conn.Close();
                    }
                    catch
                    { }
                }
                #endregion
            }
        }
        #endregion

        #region 查询
        private DataSet HelperQuery(string cmdText, SortedList SLData)
        {
            string connStr = ConfigurationManager.AppSettings["YTBZ_CONN"].ToString();
            OracleConnection conn = null;
            DataSet dsBack = new DataSet();
            try
            {
                #region cmdExec
                OracleCommand cmd = new OracleCommand();
                conn = new OracleConnection(connStr);
                conn.Open();
                if (SLData == null)
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = cmdText;
                    cmd.ExecuteNonQuery();
                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    da.TableMappings.Add("JIEYUANTABLE", "JIEYUANTABLE");
                    da.Fill(dsBack);
                }
                else
                {
                    OracleParameter[] commandParameters = getParame(conn, cmdText);
                    setParame(SLData, ref commandParameters);

                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = cmdText;
                    if (commandParameters != null)
                    {
                        foreach (OracleParameter parm in commandParameters)
                        {
                            cmd.Parameters.Add(parm);
                        }
                    }
                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    da.TableMappings.Add("JIEYUANTABLE", "JIEYUANTABLE");
                    da.Fill(dsBack);
                }
                return dsBack;
                #endregion
            }
            catch (Exception ex)
            {
                txtSaveErro(" 存储过程 "+cmdText +" 参数 "+ SDListToStr(SLData) + " 错误 " + ex.ToString());
                throw ex;
            }
            finally
            {
                #region connClose
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    try
                    {
                        conn.Close();
                    }
                    catch
                    { }
                }
                #endregion
            }
        }
        #endregion

        #region 参数
        private Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
        public void ClearCash()
        {
            paramCache.Clear();
        }
        private OracleParameter[] getParame(OracleConnection connection, string spName)
        {
            //SELECT PACKAGE_NAME, OBJECT_NAME, IN_OUT, ARGUMENT_NAME, DATA_TYPE FROM USER_ARGUMENTS WHERE OBJECT_NAME LIKE '%USERPASSRESET%'
            OracleParameter[] cachedParameters;
            cachedParameters = paramCache[spName] as OracleParameter[];
            if (cachedParameters == null)
            {
                string[] strTT = spName.Split('.');
                string strSql = "SELECT IN_OUT, ARGUMENT_NAME, DATA_TYPE FROM USER_ARGUMENTS "
                              + " WHERE UPPER(PACKAGE_NAME)=UPPER('" + strTT[0] + "')"
                              + "   AND UPPER(OBJECT_NAME) =UPPER('" + strTT[1] + "')";
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connection;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;
                cmd.ExecuteNonQuery();
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.TableMappings.Add("JIEYUANTABLE", "JIEYUANTABLE");
                DataSet dsBack = new DataSet();
                da.Fill(dsBack);
                if (dsBack != null && dsBack.Tables.Count > 0 && dsBack.Tables[0].Rows.Count > 0)
                {
                    int prameCount = dsBack.Tables[0].Rows.Count;
                    OracleParameter[] discoveredParameters = new OracleParameter[prameCount];
                    for (int i = 0; i < prameCount; i++)
                    {
                        DataRow drPrame = dsBack.Tables[0].Rows[i];
                        //IN_OUT, ARGUMENT_NAME, DATA_TYPE;
                        //REF CURSOR  NUMBER  DATE VARCHAR2  
                        if (drPrame["DATA_TYPE"].ToString() == "REF CURSOR")
                        {
                            discoveredParameters[i] = new OracleParameter(drPrame["ARGUMENT_NAME"].ToString(), OracleType.Cursor);
                        }
                        if (drPrame["DATA_TYPE"].ToString() == "NUMBER")
                        {
                            discoveredParameters[i] = new OracleParameter(drPrame["ARGUMENT_NAME"].ToString(), OracleType.Number);
                        }
                        if (drPrame["DATA_TYPE"].ToString() == "DATE")
                        {
                            discoveredParameters[i] = new OracleParameter(drPrame["ARGUMENT_NAME"].ToString(), OracleType.DateTime);
                        }
                        if (drPrame["DATA_TYPE"].ToString() == "VARCHAR2")
                        {
                            discoveredParameters[i] = new OracleParameter(drPrame["ARGUMENT_NAME"].ToString(), OracleType.VarChar);
                        }
                        if (drPrame["IN_OUT"].ToString() == "OUT")
                        {
                            discoveredParameters[i].Direction = ParameterDirection.Output;
                            if (drPrame["DATA_TYPE"].ToString() == "VARCHAR2")
                            { discoveredParameters[i].Size = 1024;  }
                        } 

                    }
                    foreach (OracleParameter discoveredParameter in discoveredParameters)
                    {
                        discoveredParameter.Value = DBNull.Value;
                    }
                    paramCache[spName] = discoveredParameters;
                    cachedParameters = discoveredParameters;
                }
            }
            OracleParameter[] clonedParameters = new OracleParameter[cachedParameters.Length];
            for (int i = 0, j = cachedParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (OracleParameter)((ICloneable)cachedParameters[i]).Clone();
            }
            return clonedParameters;
        }

        //win2012 不能使用
        private OracleParameter[] getParameOLD(OracleConnection connection, string spName)
        {
            string hashKey = connection.ConnectionString + ":" + spName;
            OracleParameter[] cachedParameters;
            cachedParameters = paramCache[hashKey] as OracleParameter[];
            //if (cachedParameters == null)
            {
                OracleCommand cmd = new OracleCommand(spName, connection);
                cmd.CommandType = CommandType.StoredProcedure;

                OracleCommandBuilder.DeriveParameters(cmd);
                OracleParameter[] discoveredParameters = new OracleParameter[cmd.Parameters.Count];
                cmd.Parameters.CopyTo(discoveredParameters, 0);
                foreach (OracleParameter discoveredParameter in discoveredParameters)
                {
                    discoveredParameter.Value = DBNull.Value;
                }
                paramCache[hashKey] = discoveredParameters;
                cachedParameters = discoveredParameters;
            }

            OracleParameter[] clonedParameters = new OracleParameter[cachedParameters.Length];
            for (int i = 0, j = cachedParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (OracleParameter)((ICloneable)cachedParameters[i]).Clone();
            }
            return clonedParameters;
        }

        private void setParame(SortedList SLParamsTT, ref OracleParameter[] myParams)
        {
            SortedList SLParams = new SortedList();
            foreach (DictionaryEntry element in SLParamsTT)
            {
                SLParams[element.Key.ToString().ToUpper()] = element.Value;
            }

            for (int i = 0; i < myParams.Length; i++)
            {
                if (myParams[i].Direction == ParameterDirection.Output) { continue; }
                string paramName = myParams[i].ParameterName.ToUpper();//参数名
                object paramValue = SLParams[paramName];

                if (paramValue == null || paramValue.ToString() == "")
                {
                    if (paramName == "V_CURRPAGESIZE") { myParams[i].Value = 65000;  }
                    if (paramName == "V_CURRPAGE") { myParams[i].Value = 0; }

                    continue;
                }

                OracleType dataType = myParams[i].OracleType;//数据类型
                if (dataType == OracleType.Int16 || dataType == OracleType.Int32)
                {
                    try { myParams[i].Value = Convert.ToInt32(paramValue); }
                    catch { throw new Exception("转换整数格式字段出错！"); }
                }
                else if (dataType == OracleType.Double || dataType == OracleType.Number || dataType == OracleType.Float)
                {
                    try { myParams[i].Value = Convert.ToDecimal(paramValue); }
                    catch { throw new Exception("转换浮点数格式字段出错！"); }
                }
                else if (dataType == OracleType.DateTime)
                {
                    try { myParams[i].Value = Convert.ToDateTime(paramValue); }
                    catch { throw new Exception("转换日期格式字段出错！"); }
                } 
                else
                {
                    myParams[i].Value = paramValue.ToString().Trim();
                }
            }
        }
        #endregion

        #region SDListToStr
        public static string SDListToStr(SortedList SDList)
        {
            if (SDList == null) { return ""; }
            string strBack = "";
            foreach (DictionaryEntry element in SDList) { strBack += element.Key.ToString().ToUpper() + ":" + element.Value + "$"; }
            return strBack;
        }
        #endregion

        #endregion

        #region 防注入
        public string sqlGetPro(string strIn)
        {
            strIn = strIn.Trim();
            string strKeyS = "and|exec|insert|select|delete|update|chr|mid|master|or|truncate|char|declare|join|cmd|;|'|--";// 
            foreach (string strKey in strKeyS.Split('|'))
            {
                strIn = strIn.Replace(strKey, "");
            }
            strIn = strIn.Replace("\r", "");
            strIn = strIn.Replace("\n", "");
            strIn = strIn.Replace("\t", "");
            return strIn;
        }
        //string sql = "action,add,admin,aggregate,all,alter,after,and,as,asc,avg,avg_row_length,auto_increment,between,bigint,bit,binary," +
        //                "blob,bool,both,by,cascade,case,char,character,change,check,checksum,column,columns,comment,constraint,create," +
        //                "cross,current_date,current_time,current_timestamp,data,database,databases,date,,datetime,day,day_hour,day_minute," +
        //                "day_second,dayofmonth,dayofweek,dayofyear,dec,decimal,default,delayed,delay_key_write,delete,desc,describe," +
        //                "distinct,distinctrow,double,drop,end,else,escape,escaped,,enclosed,enum,exec,execute,explain,exists,fields,file,first,float," +
        //                "float4,float8,flush,foreign,from,for,full,function,global,grant,grants,group,having,heap,high_priority,hour,," +
        //                "hour_minute,hour_second,hosts,identified,ignore,in,index,infile,inner,insert,insert_id,int,,integer,interval,int1,int2," +
        //                "int3,int4,int8,into,,if,is,isam,join,key,keys,kill,last_insert_id,,leading,left,length,like,lines,limit,load,local" +
        //                "lock,logs,long,longblob,longtext,low_priority,max,max_rows,match,mediumblob,mediumtext,mediumint,,middleint,min_rows," +
        //                "minute,minute_second,modify,month,monthname,myisam,natural,numeric,no,not,null,on,optimize,option,optionally,or,order,outer," +
        //                "outfile,pack_keys,partial,password,precision,primary,proc,procedure,process,processlist,privileges,read,real,references,reload" +
        //                "regexp,rename,replace,restrict,return,returns,revoke,rlike,row,rows,second,select,set,show,shutdown,smallint,soname," +
        //                "smallint,soname,sql_big_tables,sql_big_selects,sql_low_priority_updates,sql_log_off,sql_log_update,sql_select_limit" +
        //                "sql_small_result,sql_big_result,sql_warnings,straight_join,starting,status,string,table,tables,temporary,terminated,text " +
        //                "then,time,timestamp,tinyblob,tinytext,tinyint,trailing,to,type,use,using,unique,unlock,unsigned,update,usage,values,varchar " +
        //                "variables,varying,varbinary,with,write,when,where,year,year_month,zerofill ";
        #endregion

        // /////////////////////////////////////////////

        #region 文件操作
        public string txtReadFromFile(string fileName, string strFileDirt)
        {
            if (fileName == "")
            {
                return "";
            }
            string strPath = ConfigurationManager.AppSettings["YTBZ_FILE"].ToString()
                    + (strFileDirt == "" ? "" : "/" + strFileDirt + "/") + fileName;
            strPath = System.Web.HttpContext.Current.Server.MapPath(strPath);
            if (!File.Exists(strPath))
            {
                return "";
            }
            if (File.Exists(strPath))
            {
                //读取指定的文本文件,并支持中文编码字符
                StreamReader objReader = new StreamReader(strPath, System.Text.Encoding.GetEncoding("gb2312"));
                string aa = objReader.ReadToEnd();
                objReader.Close();//关闭流 
                return aa;
            }
            else
            {
                return "";
            }
        }
        public string txtReadFromFileLoc(string strPath)
        {
            if (!File.Exists(strPath))
            {
                return "";
            }
            if (File.Exists(strPath))
            {
                //读取指定的文本文件,并支持中文编码字符
                StreamReader objReader = new StreamReader(strPath, System.Text.Encoding.GetEncoding("UTF-8"));
                string aa = objReader.ReadToEnd();
                objReader.Close();//关闭流 
                return aa;
            }
            else
            {
                return "";
            }
        }
        public void txtSaveToFile(string strText, string fileName, string strFileDirt)
        {
            string strPath = ConfigurationManager.AppSettings["YTBZ_FILE"].ToString()
                    + (strFileDirt == "" ? "" : "/" + strFileDirt + "/");
            strPath = System.Web.HttpContext.Current.Server.MapPath(strPath);
            if (Directory.Exists(strPath) == false)
            {
                Directory.CreateDirectory(strPath);
            }
            strPath = strPath + fileName;
            if (File.Exists(strPath) && strFileDirt != "ERRO")
            {
                File.Delete(strPath);
            }
            txtSaveToFile(strText, strPath);
        }
        public void txtSaveToFile(string strText, string strPath)
        {
            FileStream fs = new FileStream(strPath, FileMode.OpenOrCreate, FileAccess.Write);
            StreamWriter m_streamWriter = new StreamWriter(fs);
            m_streamWriter.Flush();  // 使用StreamWriter来往文件中写入内容
            m_streamWriter.BaseStream.Seek(0, SeekOrigin.End);
            m_streamWriter.Write(strText);
            m_streamWriter.Close();
        }
        public void txtSaveErro(string strErro)
        {
            string strDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            txtSaveToFile(strDateTime + "日志错误：" + strErro+"\r\n", strDateTime.Substring(0, 10) + ".txt", "ERRO");
        }
        public void txtSaveIOErro(string strErro)
        {
            string strDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string strText = strDateTime + "日志错误：" + strErro + "\r\n";
            string fileName = strDateTime.Substring(0, 10) + ".txt";
            string strPath = ConfigurationManager.AppSettings["YTBZ_IO"].ToString()+ "/ERRO/";
            if (Directory.Exists(strPath) == false)
            {
                Directory.CreateDirectory(strPath);
            }
            strPath = strPath + fileName;
            txtSaveToFile(strText, strPath);

        }
        #endregion

        #region 密码加密
        public string MD5Encrypt(string strIn)
        {
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] res = md5.ComputeHash(Encoding.UTF8.GetBytes(strIn), 0, strIn.Length);
            char[] temp = new char[res.Length];
            System.Array.Copy(res, temp, res.Length);
            return new String(temp);
        }
        public string MD5PHP(string password)
        {
            byte[] textBytes = System.Text.Encoding.Default.GetBytes(password);
            try
            {
                System.Security.Cryptography.MD5CryptoServiceProvider cryptHandler;
                cryptHandler = new System.Security.Cryptography.MD5CryptoServiceProvider();
                byte[] hash = cryptHandler.ComputeHash(textBytes);
                string ret = "";
                foreach (byte a in hash)
                {
                    if (a < 16)
                        ret += "0" + a.ToString("x");
                    else
                        ret += a.ToString("x");
                }
                return ret;
            }
            catch
            {
                throw;
            }
        }
        #endregion

        #region xml、DataSet相互
        public static DataSet ConvertXMLToDataSet2(string xmlData)
        {

            StringReader stream = null;
            XmlTextReader reader = null;
            try
            {
                DataSet xmlDS = new DataSet();
                stream = new StringReader(xmlData);
                //从stream装载到XmlTextReader
                reader = new XmlTextReader(stream);
                //用ReadXml读取，XmlReadMode 设置为ReadSchema读取数据同时读取框架
                xmlDS.ReadXml(reader, XmlReadMode.Auto);
                return xmlDS;
            }
            catch (Exception ex)
            {
                string tt = ex.ToString();
                return null;
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }
        public static string ConvertDataSetToXML2(DataSet xmlDS)
        {
            MemoryStream stream = null;
            XmlTextWriter writer = null;


            try
            {
                stream = new MemoryStream();
                //从stream装载到XmlTextReader
                writer = new XmlTextWriter(stream, Encoding.Unicode);
                //用WriteXml方法写入文件,XmlWriteMode设置为WriteSchema写入数据同时写入框架.
                xmlDS.WriteXml(writer);
                int count = (int)stream.Length;
                byte[] arr = new byte[count];
                stream.Seek(0, SeekOrigin.Begin);
                stream.Read(arr, 0, count);

                UnicodeEncoding utf = new UnicodeEncoding();
                return utf.GetString(arr).Trim();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (writer != null) writer.Close();
            }
        }

        #region 将DataTable转化为XML
        public static string ConvertDataTableToXML(DataTable xmlDS)
        {
            MemoryStream stream = null;
            XmlTextWriter writer = null;

            try
            {
                stream = new MemoryStream();
                //从stream装载到XmlTextReader
                writer = new XmlTextWriter(stream, Encoding.Unicode);
                //用WriteXml方法写入文件,XmlWriteMode设置为WriteSchema写入数据同时写入框架.
                xmlDS.WriteXml(writer, XmlWriteMode.WriteSchema);
                int count = (int)stream.Length;
                byte[] arr = new byte[count];
                stream.Seek(0, SeekOrigin.Begin);
                stream.Read(arr, 0, count);

                UnicodeEncoding utf = new UnicodeEncoding();
                return utf.GetString(arr).Trim();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (writer != null) writer.Close();
            }
        }
        #endregion

        //将xml文件转换为DataSet
        /// <summary>
        /// 将xml文件转换为DataSet
        /// </summary>
        /// <param name="xmlFile"></param>
        /// <returns></returns>
        public static DataSet ConvertXMLFileToDataSet(string xmlFile)
        {
            StringReader stream = null;
            XmlTextReader reader = null;
            try
            {
                XmlDocument xmld = new XmlDocument();
                xmld.Load(xmlFile);

                DataSet xmlDS = new DataSet();
                stream = new StringReader(xmld.InnerXml);
                //从stream装载到XmlTextReader
                reader = new XmlTextReader(stream);
                //用ReadXml读取，XmlReadMode 设置为ReadSchema读取数据同时读取框架
                xmlDS.ReadXml(reader, XmlReadMode.Auto);
                //xmlDS.ReadXml(xmlFile);
                return xmlDS;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }
        //将DataSet转换为xml文件
        /// <summary>
        /// 将DataSet转换为xml文件
        /// </summary>
        /// <param name="xmlDS"></param>
        /// <param name="xmlFile"></param>
        public static void ConvertDataSetToXMLFile(DataSet xmlDS, string xmlFile)
        {
            MemoryStream stream = null;
            XmlTextWriter writer = null;

            try
            {
                stream = new MemoryStream();
                //从stream装载到XmlTextReader
                writer = new XmlTextWriter(stream, Encoding.Unicode);

                //用WriteXml方法写入文件,XmlWriteMode设置为WriteSchema写入数据同时写入框架.
                xmlDS.WriteXml(writer, XmlWriteMode.WriteSchema);
                int count = (int)stream.Length;
                byte[] arr = new byte[count];
                stream.Seek(0, SeekOrigin.Begin);
                stream.Read(arr, 0, count);

                //返回Unicode编码的文本


                UnicodeEncoding utf = new UnicodeEncoding();
                StreamWriter sw = new StreamWriter(xmlFile);
                sw.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
                sw.WriteLine(utf.GetString(arr).Trim());
                sw.Close();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (writer != null) writer.Close();
            }
        }

        #endregion

        #region 取唯一值
        #region 获取唯一的19位值
        /// <summary>
        /// 获取唯一的19位值
        /// </summary>
        /// <returns></returns>
        public string GenerateIntID()
        {
            byte[] buffer = Guid.NewGuid().ToByteArray();
            return BitConverter.ToInt64(buffer, 0).ToString();
        }
        #endregion

        #region 获取唯一的16位值
        /// <summary>
        /// 获取唯一的16位值
        /// </summary>
        /// <returns></returns>
        public string GenerateStringID()
        {
            long i = 1;
            foreach (byte b in Guid.NewGuid().ToByteArray())
            {
                i *= ((int)b + 1);
            }
            return string.Format("{0:x}", i - DateTime.Now.Ticks);
        }

        #endregion
        #endregion

    }

}